

import com.mongodb.MongoClient;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.bson.Document;

import javax.print.Doc;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class BankOutput {
    /**
     * 银行理财导出
     * @param args
     * @throws IOException
     */
    public static void main(String[] args) throws IOException {
        MongoClient mongoClient = new MongoClient("192.168.1.173",27017);
        final MongoCollection<Document> collection = mongoClient.getDatabase("CFSD_2021").getCollection("银行理财");

        FindIterable<Document> documents = collection.find();
        int index = 0;
        int excel_index = 0;
        List<Document> list = new ArrayList<>();
        for (Document document : documents){
            list.add(document);
            if (++index == 500000){
                WriteToExcel(list, "银行理财_1");
                list.clear();
            }
        }
        WriteToExcel(list, "银行理财_2");
    }

    public static void WriteToExcel(List<Document> documents, String excelName) throws IOException {
        Workbook workbook = new SXSSFWorkbook(600000);
        Sheet sheet = workbook.createSheet();
        Row row = sheet.createRow(0);
        int index = 0;
        row.createCell(index++).setCellValue("产品名称");
        row.createCell(index++).setCellValue("发行银行");
        row.createCell(index++).setCellValue("币种");
        row.createCell(index++).setCellValue("发行日");
        row.createCell(index++).setCellValue("停售日");
        row.createCell(index++).setCellValue("起始金额");
        row.createCell(index++).setCellValue("预期收益率");
        row.createCell(index++).setCellValue("与同期储蓄比");
        row.createCell(index++).setCellValue("收益类型");
        row.createCell(index++).setCellValue("销售状态");
        row.createCell(index++).setCellValue("发行起始日");
        row.createCell(index++).setCellValue("发行截止日");
        row.createCell(index++).setCellValue("收益计起日");
        row.createCell(index++).setCellValue("到期日");
        row.createCell(index++).setCellValue("是否保本");
        row.createCell(index++).setCellValue("委托递增单位(元)");
        row.createCell(index++).setCellValue("委托管理期限");
        row.createCell(index++).setCellValue("付息周期");
        row.createCell(index++).setCellValue("是否能被质押");
        row.createCell(index++).setCellValue("是否有权提前赎回");
        row.createCell(index++).setCellValue("流动性评级");
        row.createCell(index++).setCellValue("风险评级");
        row.createCell(index++).setCellValue("产品管理费");
        row.createCell(index++).setCellValue("投资标的");
        row.createCell(index++).setCellValue("最高收益率");
        row.createCell(index++).setCellValue("收益率说明");
        row.createCell(index++).setCellValue("收益计算基础");
        row.createCell(index++).setCellValue("本息返还方式");


        int rownum = 1;
        for (Document document : documents) {
            int cellnum = 0;


            Row row1 = sheet.createRow(rownum++);
            row1.createCell(cellnum++).setCellValue(document.get("产品名称").toString());
            row1.createCell(cellnum++).setCellValue(document.get("发行银行").toString());
            try {
                row1.createCell(cellnum++).setCellValue(document.get("币种").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("发行日").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("停售日").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("起始金额").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("预期收益率").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("与同期储蓄比").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("收益类型").toString().replace("收益类型：",""));
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("销售状态").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("发行起始日").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("发行截止日").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("收益计起日").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("到期日").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("是否保本").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("委托递增单位").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("委托管理期限").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("付息周期").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("是否能被质押").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("是否有权提前赎回").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("流动性评级").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("风险评级").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("产品管理费").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("投资标的").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("最高收益率").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("收益率说明").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("收益计算基础").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }
            try {
                row1.createCell(cellnum++).setCellValue(document.get("本息返还方式").toString());
            } catch (Exception e) {
                row1.createCell(cellnum).setCellValue("");
            }


            System.out.println("正在写入------------------------------");
        }
        workbook.write(new FileOutputStream("D:\\测试临时文件\\CFSD数据\\"+excelName));
        System.out.println(excelName + "导出成功");
    }
}
